package net.sql.config;

import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.dom4j.Element;

import net.dotool.NumberTools;
import net.dotool.StringTools;
import net.sql.SqlIf;
import net.sql.bean.SqlParam;

/**
 * 
 * @author 李岩飞
 * @email eliyanfei@126.com
 * @date 2018年4月13日下午2:15:11
 */
public class SQLBean {
	private String returnType;// 返回类型
	private String id;// 唯一标识
	private String sql;
	private String dbName;// 数据库
	private String project;// 区分项目
	private String desc;// 功能描述
	private String table;// 表名字
	private boolean cache;// 是否缓存数据
	private boolean query;// 是否为查询，默认为true
	private Map<String, IfBean> ifMap = new LinkedHashMap<String, IfBean>();// 条件信息
	private Map<String, TableBean> tableMap = new LinkedHashMap<String, TableBean>();// 表名字信息
	private boolean bean = false;// 转为为Bean对象,非map数据
	private static Pattern p = Pattern.compile("(?<=\\#\\{)(.+?)(?=\\})");
	private static Pattern sqlP = Pattern.compile(
			"(\\b(select|update|and|or|delete|insert|trancate|char|into|substr|ascii|declare|exec|count|master|into|drop|execute)\\b)");
	private String source;// 区分数据库

	public SQLBean(String project, String namespace, String source, String dbname, Element ele) {
		this.id = ele.attributeValue("id");
		if (StringTools.isNotEmpty(namespace)) {
			this.id = namespace + "_" + this.id;
		}
		this.project = StringTools.trimNull(ele.attributeValue("project"), project);
		if (StringTools.isNotEmpty(this.project)) {
			this.id = this.project + "_" + this.id;
		}
		this.source = StringTools.trimNull(ele.attributeValue("source"), source);
		this.source = StringTools.trimNull(source, "");

		this.desc = ele.attributeValue("desc");
		this.dbName = StringTools.trimNull(ele.attributeValue("dbname"), dbname);
		this.returnType = ele.attributeValue("returnType");
		this.cache = NumberTools.toBoolean(ele.attributeValue("cache"), false);
		this.query = NumberTools.toBoolean(ele.attributeValue("query"), false);
		this.sql = ele.getText().trim().trim();
		// if (StringTools.isBlank(this.returnType)) {
		// this.returnType = "java.util.HashMap";
		// }
		List<Element> ifele = ele.elements("if");
		for (Element ife : ifele) {
			IfBean where = new IfBean(ife);
			ifMap.put(where.id, where);
		}
		List<Element> tableele = ele.elements("table");
		for (Element tablee : tableele) {
			TableBean tableBean = new TableBean(tablee);
			tableMap.put(tablee.attributeValue("id"), tableBean);
		}

		try {
			Object beanObj = Class.forName(returnType).newInstance();
			if (!(beanObj instanceof Map)) {
				bean = true;
			}
		} catch (Exception e) {
		}
	}

	public String getSource() {
		return source;
	}

	/**
	 * 解析SQL语句
	 */
	public String parserSQL(SqlParam sqlParam) {
		String sql = sqlParam.getSql();
		if (StringTools.isBlank(sql)) {
			sql = this.sql;
		}
		if (StringTools.isNotBlank(sqlParam.getMySelect())) {
			sql = sql.replace("$myselect", sqlParam.getMySelect());
		}
		if (StringTools.isNotBlank(sqlParam.getMyTable())) {
			sql = sql.replace("$mytable", sqlParam.getMyTable());
		}

		if (StringTools.isNotBlank(sqlParam.getMyOrderby())) {
			sql = sql.replace("$orderby", sqlParam.getMyOrderby());
		}

		String where = "";
		String tableId = sqlParam.getTableId();
		SqlIf sqlIf = new SqlIf(sqlParam, ifMap);
		try {
			for (IfBean w : ifMap.values()) {
				if (sqlIf.test(w.id)) {
					if (StringTools.isBlank(w.table) || (StringTools.isNotBlank(w.table) && w.table.equals(tableId))) {
						where += w.text;
						if (w.value != null) {
							sqlParam.addParams(sqlParam.getParams().get(w.value));
						}
					}
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		sql = sql.replace("$where", where);
		if (StringTools.isNotBlank(tableId)) {
			TableBean tableBean = tableMap.get(tableId);
			sql = sql.replace("$table2", tableBean.getTablename2());
			sql = sql.replace("$table3", tableBean.getTablename3());
			sql = sql.replace("$table", tableBean.getTablename());
			sql = sql.replace("$select", tableBean.select);
			sql = sql.replace("$groupby", tableBean.groupby);
			sql = sql.replace("$orderby", tableBean.orderby);
		}

		Matcher matcher = p.matcher(sql);
		while (matcher.find()) {
			String name = matcher.group();
			String value = StringTools.trimNull(sqlParam.getParams().get(name), "");

			Matcher isMatch = sqlP.matcher(value);
			if (isMatch.find()) {
				System.out.println("sql注入:" + isMatch.group());
				sql = sql.replace("#{" + name + "}", "");
			} else {
				sql = sql.replace("#{" + name + "}", value);
			}
		}
		return sql;
	}

	public class TableBean {
		public String select;
		public String groupby;
		public String orderby;
		public String tablename;
		public String tablename2;
		public String tablename3;

		public TableBean(Element ele) {
			this.tablename = StringTools.trimNull(ele.attributeValue("tablename"), "");
			this.tablename2 = StringTools.trimNull(ele.attributeValue("tablename2"), "");
			this.tablename3 = StringTools.trimNull(ele.attributeValue("tablename3"), "");
			this.groupby = StringTools.trimNull(ele.attributeValue("groupby"), "");
			this.orderby = StringTools.trimNull(ele.attributeValue("orderby"), "");
			this.select = StringTools.trimNull(ele.getText(), "").trim();
		}

		public String getTablename() {
			return tablename;
		}

		public String getTablename2() {
			return tablename2;
		}

		public String getTablename3() {
			return tablename3;
		}
	}

	public class IfBean {
		public String id;
		public String table;
		public String text;
		public String test;
		public String value;

		public IfBean(Element ele) {
			this.id = "test" + (SQLConfig.INDEX++);
			this.table = ele.attributeValue("table");
			this.value = ele.attributeValue("value");
			this.test = ele.attributeValue("test");
			this.text = ele.getText();
		}
	}

	public boolean isQuery() {
		return query;
	}

	public String getReturnType() {
		return returnType;
	}

	public String getId() {
		return id;
	}

	public String getDbName() {
		return dbName;
	}

	public String getTable() {
		return table;
	}

	public boolean isCache() {
		return cache;
	}

	public Map<String, IfBean> getIfMap() {
		return ifMap;
	}

	public Map<String, TableBean> getTableMap() {
		return tableMap;
	}

	public boolean isBean() {
		return bean;
	}

	public String getDesc() {
		return desc;
	}

}
